/**
 * CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

package com.cmcc.zysoft.infocollection.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import com.cmcc.zysoft.infocollection.model.News;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;

/**
 * @author 袁凤建
 * <br />邮箱: yuan.fengjian@ustcinfo.com
 * <br />描述: NewsPcDao.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-8-13 上午9:45:45
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

@Repository
public class NewsPcDao extends HibernateBaseDaoImpl<News, String> {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取新闻分页列表信息.
	 * @param keyword 搜索关键字
	 * @param page
	 * @param rows
	 * @return map
	 */
	public Map<String, Object> getNewsList(String keyword, int page, int rows) {
		int offset = (page - 1) * rows;
		Map<String, Object> map = new HashMap<String, Object>();
		String rowSql = 
				" SELECT news.news_id AS newsId, news.title AS title, news.content AS content, " +
				" news.publish_time AS publishTime, news.state AS state, news.status AS 'status', " +
				" (SELECT a.real_name FROM tb_c_system_user a WHERE a.user_id = news.publish_man AND a.del_flag = '0') AS publishMan " +
				" FROM tb_b_news news " +
				" WHERE news.status = '0' ";
		String countSql = "SELECT COUNT(*) FROM tb_b_news news WHERE news.status = '0'";
		if(StringUtils.hasText(keyword)) {
			rowSql += " AND news.title like '%" + keyword + "%'";
			countSql += " AND news.title like '%" + keyword + "%'";
		}
		rowSql += " ORDER BY news.publish_time DESC LIMIT ?, ? ";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(rowSql, offset, rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", list);
		map.put("total", total);
		return map;
	}
	
	/**
	 * 检查新闻标题是否已经存在.
	 * @param title 新闻标题
	 * @param newsId 新闻ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> check(String title, String newsId) {
		String sql = "SELECT a.* FROM tb_b_news a WHERE a.status = '0' AND a.title = ? ";
		if(StringUtils.hasText(newsId)) {
			sql += " AND a.news_id != '" + newsId + "'";
		}
		return this.jdbcTemplate.queryForList(sql, title);
	}
	
	/**
	 * 首页-通知公告.
	 * @param companyId 企业ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> news(String companyId) {
		String sql = 
				" SELECT a.news_id AS newsId, a.title AS title, a.content AS content, a.publish_time AS addTime, b.real_name AS addMan " +
				" FROM tb_b_news a, tb_c_system_user b " +
				" WHERE a.publish_man = b.user_id AND a.state = '1' AND a.status = '0' AND b.company_id = ? " +
				" ORDER BY a.publish_time DESC" +
				" LIMIT 0, 5 ";
		return this.jdbcTemplate.queryForList(sql, companyId);
	}
}